<?php
ini_set('max_execution_time', 300);
/**
 * @package   auto_occasion
 * @subpackage 
 * @author    CMS
 * @copyright 2011
 * @license    All rights reserved
 */


/**
 * Classe permettant de gerer les statistiques
 *
 */
class statSrv {

    /**
     * Fonction permettant d'avoir les statistiques
     * par mois et année
     * @return
     *
     */
    public function getStats($month, $year) {
        
        $tarif            = self::getTarifValideByDate($month, $year);
        $tarifValidePhoto = self::getTarifValideByDate($month, $year, true);
       
        return array("tarif"=>$tarif, "photo"=>$tarifValidePhoto);

    }

    /**
     * Fonction permettant d'avoir les statistiques entre deux dates
     * @return
     *
     */
    public function getStatsByDate($dateDebut, $dateFin) {
        jClasses::inc('commun~communSrv');
        $d1 = communSrv::toDateSql($dateDebut);
        $d2 = communSrv::toDateSql($dateFin);
        
        $tarif            = self::getTarifValideByDate($d1, $d2, false, true);
        $tarifValidePhoto = self::getTarifValideByDate($d1, $d2, true, true);
       
        return array("tarif"=>$tarif, "photo"=>$tarifValidePhoto);

    }


    /**
     * récupère les trois derniers mois
     * @return array
     */
    public static function getThreeLastMonthAndYear () {
        $res = new stdClass ();
        $tMois = array  (
                            "-1"=>"11",
                            "0"=>"12",
                            "1"=>"01",
                            "2"=>"02",
                            "3"=>"03",
                            "4"=>"04",
                            "5"=>"05",
                            "6"=>"06",
                            "7"=>"07",
                            "8"=>"08",
                            "9"=>"09",
                            "10"=>"10",
                            "11"=>"11",
                            "12"=>"12"
                        );

          $moisEnCours  = (int)date('m');
          $yearMinusOne = ((int)($moisEnCours - 1) < 1) ? date("Y") - 1 : date("Y");
          $yearMinusTwo = ((int)($moisEnCours - 2) < 1) ? date("Y") - 1 : date("Y");

          $res->months  = array($tMois[$moisEnCours],$tMois[$moisEnCours - 1], $tMois[$moisEnCours - 2] );
          $res->year    = array(date("Y"), $yearMinusOne, $yearMinusTwo);

          return $res;

    }

    /**
     * Récupération des tarifs valides pour un mois et une annee
     * @return array
     */
    public static function getTarifValideByDate_($month, $year, $withPhoto = false, $betweenDate = false) {

        $sqlTarifValide  = " SELECT REPLACE(o.Occas_Montantpaye, '00', '') as Occas_Montantpaye, count(*) as nombre, DATE_FORMAT(Occas_DateSaisie, '%m/%Y') as currentDate  ";
        $sqlTarifValide .= " FROM ao_voiture_occasion o "; 
        $sqlTarifValide .= " WHERE o.Occas_Partenaire = '0' "; 
        $sqlTarifValide .= " AND (o.Occas_valide = '1' OR o.Occas_valide = '2') ";
        
        if ($betweenDate) {
            // $month = $d1 && $year = $d2
            $sqlTarifValide .= " AND o.Occas_DateSaisie BETWEEN '".$month."' AND '".$year."'";
        } else {
            $sqlTarifValide .= " AND MONTH(o.Occas_DateSaisie) = '".$month."' ";
            $sqlTarifValide .= " AND YEAR(o.Occas_DateSaisie) = '".$year."' ";
        }

        if ($withPhoto)
            $sqlTarifValide .= " AND o.Occas_Fimage <> '' ";

        $sqlTarifValide .= " AND o.Occas_Estpaye = '1' ";
        $sqlTarifValide .= " AND o.Occas_Estsaisie = '1' AND o.Occas_IDpartenaire = 0 ";
        

        if ($betweenDate) {
            $sqlTarifValide .= " GROUP BY MONTH(Occas_DateSaisie), REPLACE(o.Occas_Montantpaye, '00', '')  ";
            $sqlTarifValide .= " ORDER BY  o.Occas_DateSaisie DESC, o.Occas_Montantpaye DESC" ;
        } else {
            $sqlTarifValide .= " GROUP BY REPLACE(o.Occas_Montantpaye, '00', '') ";
            $sqlTarifValide .= " ORDER BY o.Occas_Montantpaye DESC" ;
        }

        $jdb = jDb::getDbWidget();
        $result  =  $jdb->fetchAll($sqlTarifValide);

        return $result;
    }
    /**
     * Récupération des formules % au nombre de fois ou elles sont utilisées par les annonces
     * @return array
     */
    public static function getTarifValideByDate($month, $year, $withPhoto = false, $betweenDate = false) {

        $sqlTarifValide  = " SELECT COUNT(*) AS nombre, F.idFormule AS Occas_Montantpaye_, V.Occas_DateSaisie, DATE_FORMAT(V.Occas_DateSaisie, '%m/%Y') AS currentDate, AOF.titre, AOF.prix AS Occas_Montantpaye
FROM ao_annonce_formule F
INNER JOIN ao_voiture_occasion V ON V.IdOccasion = F.idOccasion
INNER JOIN ao_formule AOF ON AOF.id = F.idFormule

WHERE V.Occas_Partenaire = '0' 
AND (V.Occas_valide = '1' OR V.Occas_valide = '2' OR V.Occas_valide = '3')
";

        if ($betweenDate) {
            // $month = $d1 && $year = $d2
            $sqlTarifValide .= " AND V.Occas_DateSaisie BETWEEN '".$month."' AND '".$year."'";
        } else {
            $sqlTarifValide .= " AND MONTH(V.Occas_DateSaisie) = '".$month."' ";
            $sqlTarifValide .= " AND YEAR(V.Occas_DateSaisie) = '".$year."' ";
        }

        if ($withPhoto)
            $sqlTarifValide .= " AND V.Occas_Fimage <> '' ";

        $sqlTarifValide .= " AND V.Occas_Estpaye = '1' ";
        $sqlTarifValide .= " AND V.Occas_Estsaisie = '1' AND V.Occas_IDpartenaire = 0 ";
        

        if ($betweenDate) {
            $sqlTarifValide .= " GROUP BY MONTH(Occas_DateSaisie), F.idFormule";
            $sqlTarifValide .= " ORDER BY  V.Occas_DateSaisie DESC, F.idFormule DESC" ;
        } else {
            $sqlTarifValide .= " GROUP BY F.idFormule";
            $sqlTarifValide .= " ORDER BY F.idFormule DESC" ;
        }
        $sqlTarifValide;
        $jdb = jDb::getDbWidget();
        $result  =  $jdb->fetchAll($sqlTarifValide);

        return $result;
    }

   
}